import glob
import random

import pymysql

import eyed3


class MusicTool:
    def __init__(self):
        self.connect = pymysql.connect(host='localhost',  # 本地数据库
                                       port=3306,
                                       user='root',
                                       password='root',
                                       db='keyboard_shortcuts',
                                       charset='utf8',
                                       cursorclass=pymysql.cursors.DictCursor)  # 服务器名,账户,密码，数据库名称
        # 创建游标
        self.cur = self.connect.cursor()

    def __del__(self):
        print("音乐数据库关闭")

    # 上传音乐信息
    def upload_song(self, music_name: str, audio_path: str, music_label: str, singer: str, music_duration: int,
                    introduce="null", picture="null"):
        sql = "insert into music_info(music_name, audio_path, music_label, singer, music_duration, introduce, " \
              "picture) values(%s,%s,%s,%s,%s,%s,%s)"
        data = (music_name, audio_path, music_label, singer, music_duration, introduce, picture)
        try:
            result = self.cur.execute(sql, data)
            if result != 0:
                print("添加歌曲" + music_name + "成功")
                self.connect.commit()
                return True
            else:
                print("没有添加成功")
        except Exception as e:
            print(e)
        self.close()
        return False

    # 获取音乐信息
    def get_music_info(self, music_name: str):
        sql = 'select music_id, audio_path, singer, music_duration, music_label from music_info where music_name = %s'
        data = (music_name,)
        try:
            result = self.cur.execute(sql, data)
            if result:
                result = self.cur.fetchone()
                music_id = result["music_id"]
                singer = result["singer"]
                audio_path = result["audio_path"]
                music_duration = result["music_duration"]
                music_label = result["music_label"]
                return music_id, audio_path, singer, music_duration, music_label
            else:
                print("没有{0}歌曲".format(music_name))
        except Exception as e:
            print(e)
        self.close()
        return None, None, None, None, None

    # 增加用户播放记录
    def insert_play_record(self, user_id: int, music_id: int, listening_time: int, music_label="null"):
        sql = "insert into music_record(user_id, music_id, music_label, listening_time) values ({}, {}, '{}', {})".format(
            user_id, music_id, music_label, listening_time)
        try:
            result = self.cur.execute(sql)
            if result:
                print("用户为" + str(user_id) + ",播放为" + str(music_id) + ",时长为" + str(listening_time))
                self.connect.commit()
                return True
            else:
                print("没有添加成功")
        except Exception as e:
            print(e)
        self.close()
        return False

    # 获取一条音乐信息
    def get_one_music(self, music_id: int):
        sql = 'select music_name, audio_path, singer, music_duration from music_info where music_id={}'.format(music_id)
        music_info = []
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchone()
                for _, value in result.items():
                    music_info.append(value)
                return music_info
            else:
                print("没有这首歌")
        except Exception as e:
            print(e)
        self.close()
        return None

    # 获取全部音乐信息
    def get_all_music(self):
        all_music = []
        sql = "select music_name, audio_path, music_label, singer, music_duration from music_info"
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchall()
                for items in result:
                    one_music = []
                    for _, value in items.items():
                        one_music.append(value)
                    all_music.append(one_music)
                return all_music
            else:
                load_music("../audio")
                print("数据库没有音乐,录入完成")
        except Exception as e:
            print(e)
        self.close()
        return None

    # 获取前面十个播放量
    def get_ten_play_music(self):
        """
           result=[]
        """
        music_info = []
        sql = "select music_id, count(music_id) as count_music from music_record group by music_id order by count_music desc limit 10"
        try:
            result = self.cur.execute(sql)
            if result == 10:
                result = self.cur.fetchall()
                for items in result:
                    tmp = []
                    for _, value in items.items():
                        tmp.append(value)
                    one_music = self.get_one_music(tmp[0])
                    if one_music:
                        one_music.append(tmp[1])
                    music_info.append(one_music)
                return music_info
            else:
                # 不足7个数据，随机生成
                result = self.cur.fetchall()
                for items in result:
                    tmp = []
                    for _, value in items.items():
                        tmp.append(value)
                for i in range(10 - len(result)):
                    one_music = self.get_one_music(random.randint(1, 101))
                    if one_music:
                        one_music.append(tmp[1])
                    music_info.append(one_music)
                return music_info
        except Exception as e:
            print(e)
        self.close()
        return None

    # 获取所有音乐id--推荐模块
    def get_all_id(self):
        sql = 'select music_id from music_info'
        music_all_id = []
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchall()
                for item in result:
                    for _, value in item.items():
                        music_all_id.append(value)
                return music_all_id
        except Exception as e:
            print(e)
        self.close()
        return None

    # 音乐搜索结果匹配
    def music_search(self, music_name: str):
        sql = "select music_name, audio_path, singer, music_duration from music_info where (music_name like '%{}%') or (singer like '%{}%')".format(music_name, music_name)
        search_list = []
        try:
            # 歌名搜索
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchall()
                for items in result:
                    one_music_info = []
                    for _, value in items.items():
                        one_music_info.append(value)
                    search_list.append(one_music_info)
                return search_list
            else:
                print("没有找到关键的歌名")
        except Exception as e:
            print(e)
        self.close()
        return None

    def close(self):
        self.cur.close()
        self.connect.close()


def load_music(music_path):
    # 获取音频的相对路径
    audio_files = glob.iglob(music_path + "/*.mp3")
    for path in audio_files:
        path = path.replace("\\", "/")
        music = eyed3.load(path)
        if music:
            audio_path = path
            music_flie = path.split("/")[2]
            music_info = music_flie.split("_")
            music_name, singer, music_label = music_info
            music_label = music_label.split(".")[0]
            music_duration = int(music.info.time_secs)
            mt = MusicTool()
            mt.upload_song(music_name, audio_path[1:], music_label, singer, music_duration)
            print("录入{}成功".format(music_name))
        else:
            print(path + "失败")
            continue


if __name__ == "__main__":
    load_music("../audio/")
    m = MusicTool()
    # a = m.music_search("陈")
    # a = m.get_seven_play_music()
    print(a)

